﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace QuickWPFMonthCalendar.DB
{

    public class Connection
    {
        private static OleDbConnection conn;

        public string ErrorMessage { get; set; }

        public Connection()
        {

            conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db/diary.mdb");  // connection string change database name and password here.

        }

        public void Open()
        {
            conn.Open(); //connection must be openned
        }

        public void Close()
        {
            conn.Close(); //connection must be openned
        }


        public bool IUDProcess(string sqlQuery, List<List<Parameter>> listParameter)
        {
            bool error = false;
            if (conn.State == ConnectionState.Closed)
            {
                Open();
            }
            foreach (List<Parameter> Listpar in listParameter)
            {
                try
                {
                    using (OleDbCommand command = new OleDbCommand(sqlQuery, conn))
                    {

                        foreach (Parameter par in Listpar)
                            command.Parameters.Add(par.ParameterName, par.ParameterValue);

                        command.ExecuteNonQuery();
                    }
                }
                catch (Exception ex)
                {
                    ErrorMessage = ex.Message;
                    error= true;
                }
            }

            Close();

            return error;
        }

        /// <summary>
        /// 検索
        /// </summary>
        /// <param name="sqlQuery"></param>
        /// <param name="ds"></param>
        /// <returns></returns>
        public bool Select(string sqlQuery, ref DataSet ds)
        {
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    Open();
                        
                }


                using (OleDbDataAdapter a = new OleDbDataAdapter(sqlQuery, conn))
                {
                    a.Fill(ds);
                }
                Close();
            }
            catch (Exception ex)
            {
                ErrorMessage = ex.Message;
                return true;
            }
            finally
            {
                Close();
            }

            return false;
        }

        /// <summary>
        /// 検索
        /// </summary>
        /// <param name="sqlQuery"></param>
        /// <param name="listParameter"></param>
        /// <returns></returns>
        public bool Select(string sqlQuery, ref DataSet ds, List<Parameter> listParameter)
        {
            if (conn.State == ConnectionState.Closed)
            {
                Open();
                    
            }

            try
            {
                using (OleDbCommand command = new OleDbCommand(sqlQuery, conn))
                {
                    if (listParameter != null)
                    {
                        foreach (Parameter par in listParameter)
                        {
                            command.Parameters.Add(par.ParameterName, par.ParameterValue);
                        }
                    }

                    OleDbDataAdapter a = new OleDbDataAdapter(sqlQuery, conn);
                    a.SelectCommand = command;


                    a.Fill(ds);
                }


            }
            catch (Exception ex)
            {
                ErrorMessage = ex.Message;
                return true;
              
            }
            finally
            { Close(); }

            return false;
        }

    }
}
